package com.export.service.dynamic;

import org.springframework.stereotype.Component;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneOffset;

/**
 * 下载日志
 *
 * @author yuanqinglong
 * @since 2021/8/2 15:50
 */
@Component
public class DownloadLogDynamic {


    /**
     * 帐户日志
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param autKeyid  aut keyid
     * @param pageNow   页面现在
     * @param pageSize  页面大小
     * @return {@link String}
     */
    public String accountLog(LocalDate startTime, LocalDate endTime, String autKeyid, Integer pageNow, Integer pageSize) {
        String sql = this.publicStatement(startTime, endTime);
        sql += " AND aut_keyid = #{autKeyid} OFFSET #{pageNow} LIMIT #{pageSize}";
        return sql;
    }


    /**
     * 帐户统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param autKeyid  aut keyid
     * @return {@link String}
     */
    public String accountLogCount(LocalDate startTime, LocalDate endTime, String autKeyid) {
        String sql = this.publicCountStatement(startTime, endTime);
        sql += " AND aut_keyid = #{autKeyid} ";
        return sql;
    }


    //--------------------------------账号接口下载日志-------------------------------//

    /**
     * 帐户接口日志
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param autKeyid  账号标识
     * @param prodUid   接口id
     * @param pageNow   页面现在
     * @param pageSize  页面大小
     * @return {@link String}
     */
    public String accountProductLog(LocalDate startTime, LocalDate endTime, String autKeyid, String prodUid,
                                    Integer pageNow, Integer pageSize) {
        String sql = this.publicStatement(startTime, endTime);
        sql += " AND aut_keyid = #{autKeyid} AND p.prod_uid = #{prodUid} OFFSET #{pageNow} LIMIT #{pageSize}";
        return sql;
    }


    /**
     * 帐户接口统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param autKeyid  aut keyid
     * @return {@link String}
     */
    public String accountProductLogCount(LocalDate startTime, LocalDate endTime, String autKeyid, String prodUid) {
        String sql = this.publicCountStatement(startTime, endTime);
        sql += " AND aut_keyid = #{autKeyid} AND p.prod_uid = #{prodUid}";
        return sql;
    }


    //--------------------------------下载客户日志-------------------------------//

    /**
     * 下载客户日志
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param custId    客户id
     * @param pageNow   页面现在
     * @param pageSize  页面大小
     * @return {@link String}
     */
    public String customerLog(LocalDate startTime, LocalDate endTime, String custId,
                              Integer pageNow, Integer pageSize) {
        String sql = this.publicStatement(startTime, endTime);
        sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) = #{custId} OFFSET #{pageNow} LIMIT #{pageSize}";
        return sql;
    }


    /**
     * 客户日志统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param custId    客户id
     * @return {@link String}
     */
    public String customerLogCount(LocalDate startTime, LocalDate endTime, String custId) {
        String sql = this.publicCountStatement(startTime, endTime);
        sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) = #{custId}";
        return sql;
    }


    //--------------------------------下载客户接口日志-------------------------------//

    /**
     * 帐户接口日志
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param custId    客户id
     * @param prodUid   接口编号
     * @param pageNow   页面现在
     * @param pageSize  页面大小
     * @return {@link String}
     */
    public String customerProductLog(LocalDate startTime, LocalDate endTime, String custId, String prodUid,
                                     Integer pageNow, Integer pageSize) {
        String sql = this.publicStatement(startTime, endTime);
        sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) = #{custId} AND p.prod_uid = #{prodUid} OFFSET #{pageNow} LIMIT #{pageSize}";
        return sql;
    }


    /**
     * 客户日志统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param custId    客户id
     * @param prodUid   接口编号
     * @return {@link String}
     */
    public String customerProductLogCount(LocalDate startTime, LocalDate endTime, String custId, String prodUid) {
        String sql = this.publicCountStatement(startTime, endTime);
        sql += " AND CONCAT('RSJ',LPAD(cust_id::text,4,'0')) = #{custId} AND p.prod_uid = #{prodUid}";
        return sql;
    }



    //--------------------------------下载接口日志-------------------------------//

    /**
     * 接口日志
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param prodUid   接口编号
     * @param pageNow   页面现在
     * @param pageSize  页面大小
     * @return {@link String}
     */
    public String productLog(LocalDate startTime, LocalDate endTime,String prodUid,Integer pageNow, Integer pageSize) {
        String sql = this.publicStatement(startTime, endTime);
        sql += " AND p.prod_uid = #{prodUid} OFFSET #{pageNow} LIMIT #{pageSize}";
        return sql;
    }


    /**
     * 接口日志统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param prodUid   接口编号
     * @return {@link String}
     */
    public String productLogCount(LocalDate startTime, LocalDate endTime, String prodUid) {
        String sql = this.publicCountStatement(startTime, endTime);
        sql += " AND p.prod_uid = #{prodUid}";
        return sql;
    }



    //--------------------------------下载接口分类日志-------------------------------//

    /**
     * 接口分类日志
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param prtyFid   接口分类
     * @param pageNow   页面现在
     * @param pageSize  页面大小
     * @return {@link String}
     */
    public String productCategoryLog(LocalDate startTime, LocalDate endTime,String prtyFid,Integer pageNow, Integer pageSize) {
        String sql = this.publicStatement(startTime, endTime);
        sql += " AND prod_type_fid = #{prtyFid} OFFSET #{pageNow} LIMIT #{pageSize}";
        return sql;
    }


    /**
     * 接口分类日志统计
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @param prtyFid   接口分类
     * @return {@link String}
     */
    public String productCategoryLogCount(LocalDate startTime, LocalDate endTime, String prtyFid) {
        String sql = this.publicCountStatement(startTime, endTime);
        sql += " AND prod_type_fid = #{prtyFid}";
        return sql;
    }


    /**
     * 公用sql
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return {@link String}
     */
    private String publicStatement(LocalDate startTime, LocalDate endTime) {
        LocalDateTime startLocalDateTime = startTime.atStartOfDay(ZoneOffset.ofHours(8)).toLocalDateTime();
        LocalDateTime endLocalDateTime = endTime.atStartOfDay(ZoneOffset.ofHours(8)).toLocalDateTime();
        return "SELECT log_reqsernumber,log_orgp_id,log_ip,log_prod_uid,prod_name,aut_keyid ,CONCAT('RSJ',LPAD(cust_id::text,4,'0')) AS cust_id,cust_sname,\n" +
                "CASE op.charge_mode WHEN '1' THEN '查询计费' WHEN '2' THEN '查得计费'  WHEN '3' THEN '包企业数计费' END charge_mode," +
                "COALESCE(CAST(NULLIF(op.charge_price,'') AS VARCHAR),'-')  AS charge_price,CASE log_iscost WHEN '1' THEN '是' ELSE '否' END log_iscost," +
                "CASE query_fback_no WHEN '0' THEN '失败' WHEN '1' THEN '查无'  WHEN '2' THEN '查得' END query_fback_no ,log_reqtime,log_retime,log_qtime FROM sys.oper_log LEFT JOIN sys.sys_orgp ON orgp_id = log_orgp_id LEFT JOIN\n" +
                "sys.sys_authon ON aut_org_id = orgp_org_id LEFT JOIN sys.sys_org ON org_id = orgp_org_id LEFT JOIN sys.sys_org_prod op ON orgp_org_id = op.org_id AND op.prod_uid = oper_log.log_prod_uid\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = log_prod_uid LEFT JOIN sys.sys_cust ON cust_id = org_cust_id " +
                " WHERE log_storagetime BETWEEN '" + startLocalDateTime + "' AND '" + endLocalDateTime + "'";
    }


    /**
     * 公用统计sql
     *
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return {@link String}
     */
    private String publicCountStatement(LocalDate startTime, LocalDate endTime) {
        LocalDateTime startLocalDateTime = startTime.atStartOfDay(ZoneOffset.ofHours(8)).toLocalDateTime();
        LocalDateTime endLocalDateTime = endTime.atStartOfDay(ZoneOffset.ofHours(8)).toLocalDateTime();
        return "SELECT COUNT(*) FROM sys.oper_log LEFT JOIN sys.sys_orgp ON orgp_id = log_orgp_id LEFT JOIN\n" +
                "sys.sys_authon ON aut_org_id = orgp_org_id LEFT JOIN sys.sys_org_prod op ON orgp_org_id = op.org_id AND op.prod_uid = oper_log.log_prod_uid\n" +
                "LEFT JOIN sys.sys_prod p ON p.prod_uid = log_prod_uid LEFT JOIN sys.sys_org ON op.org_id = orgp_org_id LEFT JOIN sys.sys_cust ON cust_id = org_cust_id " +
                " WHERE log_storagetime BETWEEN '" + startLocalDateTime + "' AND '" + endLocalDateTime + "'";
    }
}



